﻿using ExcelHelper;
using ExcelHelper.ExcelModel;
using Microsoft.Extensions.DependencyInjection;
using SqlSugar;
using SqlSugarAndEntity;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace UpdateDatabase
{
    public class JobExcelExecuteModule:ImportCommonModule
    {
        public JobExcelExecuteModule()
        {
          
        }
        /// <summary>
        /// 职业库数据导入方法
        /// </summary>
        /// <param name="FilePath"></param>
        /// <param name="SheetName"></param>
        public void DoImportJob(string FilePath,string SheetName)
        {
            int MaxLevel = 3;
            int productid = 2;
            ISqlSugarClient db = SqlSugarProvider.Db();
            NpoiHelper npoi = new NpoiHelper();
            List<JobModel> list = npoi.ExcelToObject<JobModel>(FilePath, SheetName);
            var Check = CheckExcelData(list,3);
            try
            {
                if (Check.Result)
                {
                    //开启事务,下面左右的操作全部用事务进行
                    db.Ado.BeginTran();
                    //第一步,插入excel里面数据库里面没有的作者
                    InsertAuthorInfo(db, list);
                    //第二部,插入excel里面数据库没有的课程
                    InsertCourseInfo(db, list);
                    //第三部，修改execl数据源，吧名称替换成为id
                    list = ExchangeModelInfo(db, list,MaxLevel,productid);
                    //第四步,插入视频信息
                    list = InsertVideo(db, list);
                    //第五步，插入视频和课程关系
                    InsertVideo_r_Course(db, list);
                    //第六步,插入课程和分类关系表
                    InsertCourse_r_catalog(db, list);
                    //第七步,插入课程和产品关系表
                    InsertCourse_r_product(db,2);
                    //提交事务
                    int count = GetInsertDataTotal(db,MaxLevel,productid);
                    if (list.Count != count)
                    {
                        Console.WriteLine($"excel数据为{list.Count}条,导入的数据为{count}条,数据回滚!");
                        db.Ado.RollbackTran();
                    }
                    else
                    {
                        Console.WriteLine($"excel数据为{list.Count}条,导入的数据为{count}条,验证成功，提交事务");
                        db.Ado.CommitTran();
                    }
                }
                else
                {
                    Console.WriteLine(Check.ErrorMsg);
                }

            }
            catch (Exception e)
            {
                //异常就回滚
                db.Ado.RollbackTran();
                Console.WriteLine(e.Message);
            }
        }
    }
}
